package com.cho.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cho.db.SQLiteConnection;
import com.cho.vo.FileVo;
import com.cho.vo.ProjectVo;
import com.cho.vo.TableVo;

public class TableDao {
	
	public static void deleteProjectInfo(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement("DELETE FROM PROJECT_INFO WHERE PROJECT_NAME = ?");
			
			pstmt.setString(1, projectName);
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void insertProjectInfo(ProjectVo projectVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "INSERT INTO PROJECT_INFO "
					+ "            (PROJECT_NAME, "
					+ "             PROJECT_INFO, "
					+ "             REG_DATE) "
					+ "VALUES     ( ?, "
					+ "             ?, "
					+ "             datetime('now', 'localtime')) ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectVo.getProjectName());
			pstmt.setString(2, projectVo.getProjectDescript());
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void deleteFileList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement("DELETE FROM FILE_LIST WHERE PROJECT_NAME = ?");
			
			pstmt.setString(1, projectName);
			
			pstmt.execute();
			
		} catch (SQLException e) {
			System.out.println(projectName);
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void insertFileList(FileVo fileVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "INSERT INTO FILE_LIST "
					+ "            (PROJECT_NAME, "
					+ "             FILE_SEQ, "
					+ "             FILE_NAME, "
					+ "             FILE_PATH) "
					+ "VALUES      (?, "
					+ "             ?, "
					+ "             ?, "
					+ "             ?) ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, fileVo.getProjectName());
			pstmt.setInt(2, fileVo.getFileSeq());
			pstmt.setString(3, fileVo.getFileName());
			pstmt.setString(4, fileVo.getFilePath());
			
			pstmt.execute();
		} catch (SQLException e) {
			System.out.println(fileVo.toString());
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void deleteTableList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement("DELETE FROM TABLE_LIST WHERE PROJECT_NAME = ?");
			
			pstmt.setString(1, projectName);
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void insertTableList(TableVo tableVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "INSERT INTO TABLE_LIST "
					+ "            (PROJECT_NAME, "
					+ "             FILE_SEQ, "
					+ "             TABLE_SEQ, "
					+ "             TABLE_NAME, "
					+ "             CRUD_TYPE, "
					+ "             DB_LINK, "
					+ "             SOURCE_LINE) "
					+ "VALUES     (?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ? ) ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, tableVo.getProjectName());
			pstmt.setInt(2, tableVo.getFileSeq());
			pstmt.setInt(3, tableVo.getTableSeq());
			pstmt.setString(4, tableVo.getTableName());
			pstmt.setString(5, tableVo.getCrudType());
			pstmt.setString(6, tableVo.getDbLinkName());
			pstmt.setString(7, tableVo.getSourceLine());
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static List<Map<String, String>> selectFileAndTableList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT A.FILE_NAME, A.FILE_PATH, B.TABLE_NAME, B.CRUD_TYPE, B.SOURCE_LINE "
					+ "FROM FILE_LIST A, TABLE_LIST B                                                                      "
					+ "WHERE A.PROJECT_NAME = ?			                                                                   "
					+ "AND A.PROJECT_NAME = B.PROJECT_NAME                                                                 "
					+ "AND A.FILE_SEQ = B.FILE_SEQ                                                                         "
					+ "ORDER BY A.FILE_PATH                                                                                ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<Map<String, String>> selectTableCRUDList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT A.FILE_NAME,                                                   "
					+ "A.FILE_PATH,                                                          "
					+ "B.TABLE_NAME,                                                         "
					+ "B.CRUD_TYPE,                                                          "
					+ "A.FILE_SEQ,                                                   		 "
					+ "SUM( CASE CRUD_TYPE WHEN 'C' THEN 1 ELSE 0 END) CRUD_TYPE_C,          "
					+ "SUM( CASE CRUD_TYPE WHEN 'R' THEN 1 ELSE 0 END) CRUD_TYPE_R,          "
					+ "SUM( CASE CRUD_TYPE WHEN 'U' THEN 1 ELSE 0 END) CRUD_TYPE_U,          "
					+ "SUM( CASE CRUD_TYPE WHEN 'D' THEN 1 ELSE 0 END) CRUD_TYPE_D           "
					+ "FROM FILE_LIST A, TABLE_LIST B                                        "
					+ "WHERE A.PROJECT_NAME = ?		                                         "
					+ "AND A.PROJECT_NAME = B.PROJECT_NAME                                   "
					+ "AND A.FILE_SEQ = B.FILE_SEQ                                           "
					+ "GROUP BY FILE_NAME, FILE_PATH, TABLE_NAME,B.CRUD_TYPE                 "
					+ "ORDER BY A.FILE_PATH, B.TABLE_NAME,						             "
					+ "CASE B.CRUD_TYPE                                                      "
					+ "WHEN 'C' THEN 1                                                       "
					+ "WHEN 'R' THEN 2                                                       "
					+ "WHEN 'U' THEN 3                                                       "
					+ "WHEN 'D' THEN 4                                                       "
					+ "ELSE 5 END 			                                                 ";
					
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<Map<String, String>> selectFileList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT A.FILE_NAME,                                           "
					+ "A.FILE_PATH,                                                  "
					+ "A.FILE_SEQ                                                   "
					+ "FROM FILE_LIST A, TABLE_LIST B                                "
					+ "WHERE A.PROJECT_NAME = ?                                      "
					+ "AND A.PROJECT_NAME = B.PROJECT_NAME                           "
					+ "AND A.FILE_SEQ = B.FILE_SEQ                                   "
					+ "GROUP BY FILE_NAME, FILE_PATH						         "
					+ "ORDER BY A.FILE_PATH                                          ";
					
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	public static List<Map<String, String>> selectTableList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT TABLE_NAME         "
					+ "FROM TABLE_LIST           "
					+ "WHERE PROJECT_NAME = ?    "
					+ "GROUP BY TABLE_NAME       "
					+ "ORDER BY TABLE_NAME       ";
					
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	private static void resultSetAutoSetting(ResultSet rs, List<Map<String, String>> result) throws SQLException{
		Map<String, String> data = new HashMap<String, String>();
		
		ResultSetMetaData rsm = rs.getMetaData();
		int columnCount = rsm.getColumnCount();
		
		for (int i = 0; i < columnCount; i++) {
			String columnLabel = rsm.getColumnLabel(i+1);
			data.put(columnLabel, rs.getString(columnLabel));
		}
		
		result.add(data);
	}
	
	public static void main(String[] args) {
		TableDao t = new TableDao();
		
		t.selectFileAndTableList("TEST1");
	}
}
